package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Supplier;


public class DBSupplier implements IFDBSupplier {
     private  Connection con;
    public DBSupplier() {
       con = DBConnection.getInstance().getDBcon();
    }
   
    public Supplier findSupplier(int SupplierID, boolean retriveAssociation)
    {
        Supplier supObj = new Supplier();
        supObj = singleWhere("salesOrderID = " + SupplierID, retriveAssociation);
        return supObj;
    }
    private Supplier singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Supplier supObj = new Supplier();
        String query =  buildQuery(wClause);
        System.out.println("DBSupplier -singelWhere " + query); 
        try{ 
	   Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
	   int snr=0;
	   if( results.next() ){
	       supObj = buildSupplier(results);
               
           }
	   stmt.close();
	}
	catch(Exception e){
	   System.out.println("Query exception - select supplier : "+e);
	   
        }
	return supObj;

    
    }
    private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();
		
		String query =  buildQuery(wClause);
                System.out.println("DBSupplier " + query); 
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			int snr=0;
			while( results.next() ){
		     	 Supplier supObj = new Supplier();
			 supObj = buildSupplier(results);	
                         list.add(supObj);
                         
			}
			stmt.close();
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception - select supplier : "+e);
	 	}
		return list;
	}
    @SuppressWarnings("empty-statement")
    public int insertSupplier(Supplier sup)
    {  
        int nextID = GetMax.getMaxId("Select max(ID) from Supplier");
        nextID = nextID + 1;
        System.out.println("next ID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO Supplier(SupplierID, name, address, country, phoneNo, email)  VALUES('"+
	  		sup.getSupplierID()  + "','"  +
                        sup.getName()  + "','"  +
                        sup.getAddress()  + "','"  +
                        sup.getCountry()  + "','"  +
                        sup.getPhoneNo()  + "','"  +
                        sup.getEmail()  + "','"  +
                       "')";
     
	   return(rc);
	}
    
    public int updateSupplier(Supplier sup)
	{
		Supplier supObj  = sup;
		int rc=-1;

		String query="UPDATE Supplier SET "+
		 	"name ='"+ supObj.getName()+"', "+
                        "address ='"+ supObj.getAddress() + "', " +
                        "country ='"+ supObj.getCountry() + "' " +
                        "phoneNo ='"+ supObj.getPhoneNo() + "' " +
                        "email ='"+ supObj.getEmail() + "' " +
		        "WHERE supplierID = '"+ supObj.getSupplierID() + "'";
                System.out.println("Update query:" + query);
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Supplier db: "+ex);
	  	}
		return(rc);
        }
                
    private Supplier buildSupplier(ResultSet results)
    {
       Supplier supObj = new Supplier();
         try{
               supObj.setSupplierID(results.getInt(1));
               supObj.setName(results.getString(2));
               supObj.setAddress(results.getString(3));
               supObj.setCountry(results.getString(4));
               supObj.setPhoneNo(results.getInt(5));
               supObj.setEmail(results.getString(6));
               
               System.out.println("build Supplier" + supObj.getName());
          }
         catch(Exception e)
         {
             System.out.println("building supplier object");
         }
        
        return supObj;
        
    }
 
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Supplier";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

    public ArrayList getAllSupplier(boolean retriveAssociation) {
        ArrayList list = new ArrayList();
        return list;
    }

    public Supplier searchSupplierOnID(int supplierID, boolean retriveAssociation) {
       String wClause = "  SupplierID = '" + supplierID + "'";
        return singleWhere(wClause, retriveAssociation);
    }

  public Supplier searchSupplierName(String name, boolean retriveAssociation) {
       String wClause = "  SupplierName = '" + name + "'";
        return singleWhere(wClause, retriveAssociation);
    }
}